<?php

namespace app\admin\controller;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use think\Controller;
use think\Db;

class ImportData extends Controller
{
    public function product()
    {
        $filePath = dirname(dirname(dirname(dirname(__FILE__)))) . "/public/uploads/data.xlsx";
        $reader = new Xlsx();
        if (!$PHPExcel = $reader->load($filePath)) {
            $this->error(__('Unknown data format'));
        }

        $currentSheet = $PHPExcel->getSheet(1);  //读取文件中的第一个工作表
        $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
        $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
        $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);

        $insert = [];

        for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
            $values = [];
            for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                $values[] = is_null($val) ? '' : $val;
            }
            $insert[] = [
                'id' => $values[0],
                'title' => $values[1],
                'desc' => $values[2],
                'order' => $values[3],
                'sale' => $values[4],
                'price' => $values[5],
                'stock' => $values[6],
                'createtime' => time(),
                'updatetime' => time(),
            ];

            if ($currentRow % 100 == 0) {
                Db::table('product')->insertAll($insert);
                $insert = [];
            }
        }

        if (!empty($insert)) {
            Db::table('product')->insertAll($insert);
        }
        echo "插入完了";
    }
}